﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Moon.Sql
{
    public class MySqlDatabase : DatabaseProvider, IDatabase
    {
        private string _ConnectionString = null;
        private ISqlDialect _SqlDialect = null;
        public MySqlDatabase(string connectionString)
        {
            _ConnectionString = connectionString;
            _Connection = new MySqlConnection(connectionString);
            _SqlDialect = new MySqlDialect();
            DbId = $"MYSQL{DateTime.Now.Ticks}";
        }

        public IDbConnection Connection { get { return _Connection; } }
        public IDbTransaction Transaction { get { return _Transaction; } }
        public string ConnectionString { get { return _ConnectionString; } }
        public IAop Aop { get { return _Aop; } set { _Aop = value; } }
        public ISqlDialect SqlDialect { get { return _SqlDialect; } }
        public string DbId { get; private set; }
        public int CommandTimeout { get; set; } = 30;

        public int BulkInsert(string csvFilePath, string tableName, string columns)
        {
            int rowCount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting($"MySql BulkInsert,TableName={tableName}", null, ExecuteType.NonQuery);

            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                MySqlBulkLoader bulk = BulkInsertBase(csvFilePath, tableName, columns);
                rowCount = bulk.Load();
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, rowCount);
            }
            return rowCount;
        }

        public async Task<int> BulkInsertAsync(string csvFilePath, string tableName, string columns)
        {
            int rowCount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting($"MySql BulkInsert,TableName={tableName}", null, ExecuteType.NonQuery);

            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                MySqlBulkLoader bulk = BulkInsertBase(csvFilePath, tableName, columns);
                rowCount = await bulk.LoadAsync();
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, rowCount);
            }
            return rowCount;
        }

        private string GetCsvFilePath()
        {
            string parentPath = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory).Parent.FullName;
            return Path.Combine(parentPath, Guid.NewGuid().ToString("N") + ".csv");
        }

        public int BulkInsert<T>(List<T> list, string tableName, string columns)
        {
            int rowCount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting($"MySql BulkInsert,TableName={tableName}", null, ExecuteType.NonQuery);

            string tmpPath = "";
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                string csv = CommonUtils.ToCsvStr<T>(list, columns);
                tmpPath = GetCsvFilePath();
                File.WriteAllText(tmpPath, csv, Encoding.UTF8);
                MySqlBulkLoader bulk = BulkInsertBase(tmpPath, tableName, columns);
                rowCount = bulk.Load();
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                if (File.Exists(tmpPath))
                {
                    File.Delete(tmpPath);
                }
                OnExecuted(executingArgs, exception, rowCount);
            }
            return rowCount;
        }

        public async Task<int> BulkInsertAsync<T>(List<T> list, string tableName, string columns)
        {
            int rowCount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting($"MySql BulkInsert,TableName={tableName}", null, ExecuteType.NonQuery);

            string tmpPath = "";
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                string csv = CommonUtils.ToCsvStr<T>(list, columns);
                tmpPath = GetCsvFilePath();
                File.WriteAllText(tmpPath, csv, Encoding.UTF8);
                MySqlBulkLoader bulk = BulkInsertBase(tmpPath, tableName, columns);
                rowCount = await bulk.LoadAsync();
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                if (File.Exists(tmpPath))
                {
                    File.Delete(tmpPath);
                }
                OnExecuted(executingArgs, exception, rowCount);
            }
            return rowCount;
        }

        public override int BulkInsert(DataTable table, string tableName)
        {
            if (table?.Rows?.Count == 0) return 0;
            int rowCount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting($"MySql BulkInsert,TableName={tableName}", null, ExecuteType.NonQuery);

            string tmpPath = "";
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                tmpPath = GetCsvFilePath();
                string csv = CommonUtils.ToCsvStr(table);
                File.WriteAllText(tmpPath, csv, Encoding.UTF8);
                MySqlBulkLoader bulk = BulkInsertBase(tmpPath, tableName, string.Join(",", table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName)));
                tmpPath = bulk.FileName;
                rowCount = bulk.Load();
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                if (File.Exists(tmpPath))
                {
                    File.Delete(tmpPath);
                }
                OnExecuted(executingArgs, exception, rowCount);
            }
            return rowCount;
        }

        private MySqlBulkLoader BulkInsertBase(string csvFilePath, string tableName, string columns)
        {
            //开启mysql支持文件导入
            //连接字符串中 需要增加AllowLoadLocalInfile=true
            MySqlBulkLoader bulk = new MySqlBulkLoader(_Connection as MySqlConnection)
            {
                Timeout = CommandTimeout,
                FieldTerminator = ",",
                FieldQuotationCharacter = '"',
                EscapeCharacter = '"',
                LineTerminator = "\r\n",
                FileName = csvFilePath,
                NumberOfLinesToSkip = 1,//跳过第1行
                TableName = tableName,
                Local = true,
                CharacterSet = "UTF8"
            };
            bulk.Columns.AddRange(columns.Split(','));
            return bulk;
        }

        public override async Task<int> BulkInsertAsync(DataTable table, string tableName)
        {
            if (table?.Rows?.Count == 0) return 0;
            int rowCount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting($"MySql BulkInsert,TableName={tableName}", null, ExecuteType.NonQuery);

            string tmpPath = "";
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                tmpPath = GetCsvFilePath();
                string csv = CommonUtils.ToCsvStr(table);
                File.WriteAllText(tmpPath, csv, Encoding.UTF8);
                MySqlBulkLoader bulk = BulkInsertBase(tmpPath, tableName, string.Join(",", table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName)));
                tmpPath = bulk.FileName;
                rowCount = await bulk.LoadAsync();
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                if (File.Exists(tmpPath))
                {
                    File.Delete(tmpPath);
                }
                OnExecuted(executingArgs, exception, rowCount);
            }
            return rowCount;
        }

        protected override IDbCommand GetCommand(string sql, object param = null, CommandType? commandType = null, IDbTransaction transaction = null)
        {
            MySqlCommand cmd = new MySqlCommand(sql, _Connection as MySqlConnection);
            cmd.Transaction = transaction as MySqlTransaction;
            cmd.CommandType = commandType == null ? CommandType.Text : (CommandType)commandType;
            cmd.CommandTimeout = CommandTimeout;
            if (param != null)
            {
                SetupParam(cmd, param);
            }
            return cmd;
        }

        protected override IDataAdapter GetAdapter(IDbCommand command)
        {
            return new MySqlDataAdapter(command as MySqlCommand);
        }

        ~MySqlDatabase() => this.Dispose();
        int _disposeCounter;
        public void Dispose()
        {
            if (Interlocked.Increment(ref _disposeCounter) != 1) return;
            _Connection?.Close();
        }
    }
}